package computer.data;

import java.sql.*;
import java.util.*;

import computer.business.*;
import org.apache.logging.log4j.core.util.Integers;

public class ProductDB {

    //This method returns null if a product isn't found.
    public static Product selectProduct(String productCode) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM Product "
                + "WHERE ProductCode = ?";
        try {
            connection = DataSourceUtils.getConnection();
            ps = connection.prepareStatement(query);
            ps.setString(1, productCode);
            rs = ps.executeQuery();
            if (rs.next()) {
                Product p = new Product();
                p.setId(rs.getLong("ProductID"));
                p.setCode(rs.getString("ProductCode"));
                p.setDescription(rs.getString("ProductDescription"));
                p.setPrice(rs.getDouble("ProductPrice"));
                return p;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.err.println(e);
            return null;
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DataSourceUtils.freeConnection(connection);
        }
    }

    //This method returns null if a product isn't found.
    public static Product selectProduct(long productID) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM Product "
                + "WHERE ProductID = ?";
        try {
            connection = DataSourceUtils.getConnection();
            ps = connection.prepareStatement(query);
            ps.setLong(1, productID);
            rs = ps.executeQuery();
            if (rs.next()) {
                Product p = new Product();
                p.setId(rs.getLong("ProductID"));
                p.setCode(rs.getString("ProductCode"));
                p.setDescription(rs.getString("ProductDescription"));
                p.setPrice(rs.getDouble("ProductPrice"));
                return p;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.err.println(e);
            return null;
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DataSourceUtils.freeConnection(connection);
        }
    }

    //This method returns null if a product isn't found.
    public static String selectProducts() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        String query = "SELECT * FROM Product";

        try {
            connection = DataSourceUtils.getConnection();
            ps = connection.prepareStatement(query);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product p = new Product();
                p.setId((long) Integers.parseInt(rs.getString("ProductID")));
                p.setCode(rs.getString("ProductCode"));
                p.setDescription(rs.getString("ProductDescription"));
                p.setPrice(rs.getDouble("ProductPrice"));
                sb.append(p.toString() + "<--><br>");
            }
        } catch (
                SQLException e) {
            System.err.println(e);
        } finally {
            System.out.println(sb);
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DataSourceUtils.freeConnection(connection);
        }
        return sb.toString();
    }

    public static String insertProduct(Product p) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String productCode = p.getCode();
        String productDescription = p.getDescription();
        double productPrice = p.getPrice();

        String query = "INSERT INTO Product "
                + "(ProductCode, ProductDescription, ProductPrice) VALUES (?, ?, ?)";
        try {
            connection = DataSourceUtils.getConnection();
            ps = connection.prepareStatement(query);

            ps.setString(1, productCode);
            ps.setString(2, productDescription);
            ps.setDouble(3, productPrice);

            int count = ps.executeUpdate();
            if (count > 0) {
                return "OK";
            } else {
                return "NOT OK";
            }
        } catch (SQLException e) {
            System.err.println(e);
            return "NOT OK";
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DataSourceUtils.freeConnection(connection);
        }
    }
}